create table user_clicklog
(
    user_id string,
    click_time string
) row format delimited fields terminated by ","
;



with tmp2 as (
    select user_id, click_time, minute,
           nvl(minute - lag(minute) over (partition by user_id order by click_time), 0) timediff
    from (select user_id, click_time, cast(unix_timestamp(click_time) / 60 as int) minute
          from user_clicklog) tmp
)
select user_id, click_time, row_number() over (partition by user_id,sumdiff order by minute)
from (
         select user_id,
                click_time,
                minute,
                timediff,
                sum(diff)
                    over (partition by user_id order by minute rows between unbounded preceding and current row) sumdiff
         from (select user_id,
                      click_time,
                      minute,
                      timediff,
                      case
                          when timediff >= 30 then 1
                          else 0 end diff
               from tmp2
              ) tmp3
     ) tmp4;




create table user_clicklog
(
    user_id string,
    click_time string
) row format delimited fields terminated by ","
;



with tmp2 as (
    select user_id, click_time,
           nvl(minute - lag(minute) over (partition by user_id order by click_time), 0) timediff   -- 分钟差值
    from (select user_id, click_time, cast(unix_timestamp(click_time) / 60 as int) minute
          from user_clicklog) tmp
)
select user_id, click_time, row_number() over (partition by user_id,sumdiff order by click_time) click_order   -- 排序给出 rank
from (
         select user_id,
                click_time,
                timediff,
                sum(diff)
                    over (partition by user_id order by click_time rows between unbounded preceding and current row) sumdiff    -- sum 开窗聚合分开浏览时间
         from (select user_id,
                      click_time,
                      timediff,
                      case
                          when timediff >= 30 then 1
                          else 0 end diff        -- 按照30分钟分开
               from tmp2
              ) tmp3
     ) tmp4;
